# @Time : 2025/1/1 21:29
# @Author : Fioman 
# @Phone : 13149920693
"""
数据表的访问,包括创建,获取数据表的内容,删除数据表的内容
"""
from typing import List, Dict

from eap_tool.eap_logger import debug_eap
from eap_tool.mysql_operator import MysqlOperator


def create_table_pc_data_craft():
    """
    创建工艺质量表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_CRAFT" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "CONTAINER VARCHAR(50)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "ITEMVALUE VARCHAR(200)," + \
              "ISOK VARCHAR(1)," + \
              "GROUPNUM VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_craft() error: {e}")


def create_table_pc_data_alarm():
    """
    创建报警表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_ALARM" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "ALARMCODE VARCHAR(30)," + \
              "ALARMDES VARCHAR(200)," + \
              "ALARMVALUE VARCHAR(1)," + \
              "CREATETIME DATETIME" + \
              ")"

        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_alarm() error: {e}")


def create_table_pc_data_manualmaterial():
    """
    创建手动参数调整表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_MANUALMATERIAL" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "CONTAINER VARCHAR(50)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "OLDITEMVALUE VARCHAR(200)," + \
              "NEWITEMVALUE VARCHAR(200)," + \
              "ISOK VARCHAR(1)," + \
              "CREATETIME DATETIME" + \
              ")"

        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_manualmaterial() error: {e}")


def create_table_pc_data_emp():
    """
    员工上下机记录上传表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_EMP" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "EMPNO VARCHAR(50)," + \
              "EMPNAME VARCHAR(50)," + \
              "EMPCODE VARCHAR(50)," + \
              "EMPDES VARCHAR(50)," + \
              "EMPTIME DATETIME," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_emp() error: {e}")


def create_table_pc_data_status():
    """
    创建不实时采集设备状态表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "STATUSCODE VARCHAR(30)," + \
              "STATUSDES VARCHAR(200)," + \
              "STATUSVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_status() error: {e}")


def create_table_pc_data_count():
    """
    创建当前作业工单统计的数量表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_COUNT" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "COUNT INT," + \
              "UPDATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_data_count() error: {e}")


def create_table_pc_signal_status():
    """
    创建设备实时信号表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_SIGNAL_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "SIGNALCODE VARCHAR(30)," + \
              "SIGNALDES VARCHAR(200)," + \
              "SIGNALVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_pc_signal_status() error: {e}")


def create_table_eap_signal_status():
    """
    创建Eap实时信号表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists EAP_SIGNAL_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "SIGNALCODE VARCHAR(30)," + \
              "SIGNALDES VARCHAR(200)," + \
              "SIGNALVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_eap_signal_status() error: {e}")


def create_table_eap_material_data():
    """
    创建EAP资料下发表,该表为EAP下发资料表,主要为了EAP下发资料到设备
    .当设备监控到EAP下发资料到设备完成信号时,设备读取
    该表资料信息,然后自动加载到设备资料,这个表平时不需要轮询的,只有在接收到信号之后才需要去查看
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists EAP_MATERIAL_DATA" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "PRODUCT VARCHAR(50)," + \
              "CONTAINER VARCHAR(50)," + \
              "KWPRESSSOLTPERPNL VARCHAR(50)," + \
              "MATERIALCODE VARCHAR(10)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "ITEMVALUE VARCHAR(500)," + \
              "GROUPNUM BIGINT," + \
              "CREATETIME DATETIME" + \
              ")"
        try:
            sp.create_table(sql)
        except Exception as e:
            debug_eap(f"in create_table_eap_material_data() error: {e}")


def pc_data_craft_fetch_all() -> List[Dict]:
    """
    pc_data_craft 获取所有的数据
    @return:
    """
    tableName = "pc_data_craft"
    return fetch_all(tableName)


def pc_data_manual_fetch_all() -> List[Dict]:
    """
    pc_data_manualmaterial 获取所有的数据
    :return:
    """
    tableName = "pc_data_manualmaterial"
    return fetch_all(tableName)


def pc_data_alarm_craft_fetch_all() -> List[Dict]:
    """
    获取 pc_data_alarm 表数据
    :return:
    """
    tableName = "pc_data_alarm"
    return fetch_all(tableName)


def pc_data_status_fetch_all() -> List[Dict]:
    """
    获取 pc_data_status 表数据
    :return:
    """
    tableName = "pc_data_status"
    return fetch_all(tableName)


def pc_data_count_fetch_all() -> List[Dict]:
    """
    获取 pc_data_count 表数据
    :return:
    """
    tableName = "pc_data_count"
    return fetch_all(tableName)


def pc_signal_status_fetch_all() -> List[Dict]:
    """
    获取 pc_signal_status 表数据
    :return:
    """
    tableName = "pc_signal_status"
    return fetch_all(tableName)


def eap_signal_status_fetch_all() -> List[Dict]:
    """
    获取 eap_signal_status 表数据
    :return:
    """
    tableName = "eap_signal_status"
    return fetch_all(tableName)


def eap_material_data_fetch_all() -> List[Dict]:
    """
    获取 eap_material_data 表数据
    :return:
    """
    tableName = "eap_material_data"
    return fetch_all(tableName)


def pc_data_emp_fetch_all() -> List[Dict]:
    """
    获取 pc_data_emp 表数据
    :return:
    """
    tableName = "pc_data_emp"
    return fetch_all(tableName)


def eap_material_data_delete_all() -> int:
    """
    删除 eap_material_data 表数据
    :return:
    """
    try:
        tableName = "eap_material_data"
        with MysqlOperator() as sp:
            return sp.delete_data(tableName)
    except Exception as e:
        errInfo = f"in eap_material_data_delete_all error: {e}"
        debug_eap(errInfo)
        return -1


def eap_signal_status_delete_one(fieldName, fieldValue) -> int:
    """
    根据字段和值删除对应的记录,返回删除的行数
    :param fieldName:字段
    :param fieldValue:字段的值
    :return:
    """
    tableName = "eap_signal_status"
    with MysqlOperator() as sp:
        return sp.delete_data_by_field_val(tableName, fieldName, fieldValue)


def fetch_all(tableName) -> List[Dict]:
    """
    根据数据库的表名获取数据库的所有的内容,并且以字典列表的形式返回
    :param tableName:
    :return:
    """
    try:
        with MysqlOperator() as sp:
            return sp.fetch_all(tableName)
    except Exception as e:
        debug_eap(f"in fetch_all() error: {e}")
        return []


def create_total_tables():
    """
    创建所有的数据表,每次有新表的时候都要在这里添加创建新表语句
    :return:
    """
    create_table_pc_data_craft()
    create_table_pc_data_alarm()
    create_table_pc_data_manualmaterial()
    create_table_pc_data_emp()
    create_table_pc_data_status()
    create_table_pc_data_count()
    create_table_pc_signal_status()
    create_table_eap_signal_status()
    create_table_eap_material_data()


if __name__ == '__main__':
    create_total_tables()
